I am a big fan of SAS Management Console. This is the central location where you can perform many of your SAS administration tasks. One of the most important tasks of a SAS Administrator is making sure data is available to the right people when they need it. Most of my customers have data that sit in several different locations. It is very common for that data to reside on a DBMS. We can use SAS Management Console to make it easy for SAS users to work with DBMS data.

First thing to note—like many things within SAS Metadata, SAS Management Console itself is role-based. If you are handing out SAS Management Console to more people than just your SAS Administrators, you can set it up so that a user can see only the plug-ins that apply to their job. For example, a user can have a role that allows them to see the Library Manager but not the Server Manager or the User Manager plug-ins.

There are two main steps to set up access to DBMS tables. First, you register the DBMS server. Second, you register the DBMS library. A SAS administrator who has privileges to work with the Server Manager plug-in in SAS Management Console can complete the necessary tasks to set up the access to DBMS tables. For illustration purposes, I’m going to show a SQL Server DBMS. However, these steps can be generalized to any DBMS.

Register the DBMS server

First, let’s register a SQL Server inside SAS Management Console using the Server Manager plug-in.

1. Open up SAS Management Console and log in as a user that has administrator privileges. Right-click Server Manager and select the New Server option to access the New Server wizard.

1. Select the database server type from the Database Servers list. Then, click Next.

1. Enter an appropriate server name in the Name field. Click Next.

1. From the drop down menu, select the appropriate data source type if you are working with ODBC. I am selecting “ODBC- Microsoft SQL Server(PC Client)”. Accept the remaining defaults for the server properties. Click Next.

1. Specify the database vendor-specific values on the connection properties page. If the user credentials for the database are different from the credentials used to log in to SAS, then you must create an authentication domain to store valid database credentials. Click New if you wish to create the authentication domain now. Otherwise, use the drop-down menu under Authentication Domain to select the appropriate authentication. Then click Next.

1. Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the wizard settings.

Register the DBMS library

Next, following our previous example, let’s register the SQL Server library.

1. In SAS Management Console, expand Data Library Manager. Right-click Libraries and select the New Library option to access the New Library wizard.

1. Select the database type from the Database Data list. Click Next.

1. Enter an appropriate library name in the Name field. The Location field is the Metadata Folder structure. You can change it by clicking Browse. Click Next when finished.

1. Select an application server from the list, and use the right arrow to assign the application server. This step makes the library available to the server and makes the library visible to users of the server. Click Next.

1. Specify a libref on the library properties page. A libref is simply an 8 character alias to a library, and it follows standard SAS syntax (no special characters, cannot begin with a numeric). You can also click Advanced Options to perform tasks such as pre-assigning a library. However, I always recommend to my customers not to pre-assign a library if they are hitting a production database. Click Next to access the next page of the wizard.

1. On the server and connection page, select the database server we set up previously. Contact your database administrator if you are unsure of the correct value for the schema field. Click Next.

1. Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the library settings.

From here, you can register tables and work with your DBMS data the same way you would work with SAS tables!

Please note that some of the techniques discussed here require a SAS/Access engine to allow SAS to reach the data source. The above technique used the SAS/Access to ODBC engine. See
Chapter 2 of the SAS 9.3 Intelligence Platform, Data Administration Guide: Connecting to Common Data Sources.